class Betrothal < ActiveRecord::Base
  belongs_to    :character
  
  def Betrothal.find_proposals_to(character)
    # Betrothal proposals to character or one of character's vassals who are family members
    sql = Betrothal.sql_select_from_betrothal
    sql += " WHERE (" + Betrothal.sql_clause(character, "to") + ") AND (b.rejected = 0 OR b.rejected IS NULL)"
    puts sql
    return Betrothal.find_by_sql sql
  end 
  
  def Betrothal.find_proposals_from(character)
    sql = Betrothal.sql_select_from_betrothal
    sql += " WHERE " + Betrothal.sql_clause(character, "from")
    return Betrothal.find_by_sql sql
  end
  
  def accept (accepting_character)
    return false unless accepting_character
    ch_to = self.to_character
    ch_from = self.from_character
    
    return false unless ch_to.controlled_by?(accepting_character)
    
    ch_to.betroth ch_from
    
    self.destroy
  end
  
  def reject (rejecting_character)
    return false unless rejecting_character
    ch_to = self.to_character
    
    return false unless ch_to.controlled_by?(rejecting_character)
    
    self.rejected = true
    
    self.save
  end
  
  def to_character
    Character.find(self.to) unless self.to.nil? or self.to < 1
  end
  
  def from_character
    Character.find(self.from) unless self.from.nil? or self.from < 1
  end
  
  protected
  def Betrothal.sql_select_from_betrothal
    "SELECT b.id, b.character_id, b.to, b.from, b.rejected, b.created_at, b.updated_at FROM betrothals b "
  end
  
  def Betrothal.sql_clause(character, field)
    sql_titles = "SELECT id FROM titles WHERE character_id = #{character.id}"
    sql_vassals = "SELECT c.id FROM characters c, relations r WHERE " +
      "c.family_name = '#{character.family_name}' " +
      " AND c.id = r.character_id " +
      " AND r.title_id IN (#{sql_titles})"
    return " b.#{field} = #{character.id} OR b.#{field} IN (#{sql_vassals}) "
  end
end
